import pandas as pd

from src.db.dbhelper import query_sql_by_pandas, execute_sql


def insert_product():
    st = ['55A5D Pro', '55A5D Pro', '65A5D Pro', '65A5D Pro', '75A5D Pro', '75A5D Pro', '85A5D Pro', '85A5D Pro',
          '100A5D Pro', '55F1', '85A5D', '100A5D', '65A6E', '65A6E', '75A6E', '75A6E', '75A6E', '85A6E', '85A6E',
          '65A7E',
          '65A7E', '75A7E', '75A7E', '85A7E', '85A7E', '100A7E', '65A7E Pro', '75A7E Pro', '75A7E Pro', '85A7E Pro',
          '100A7E Pro', '65P6E', '75P6E', '85P6E', '100P6E', '100P60 P', '86H9E', '100H5F', '55G7D', '65G7D', '65G7D',
          '75G7D', '75G7D', '86G7D', '86G7D', '65G7E', '75G7E', '86G7E', '65H8D', '75H8D', '86H8D', 'W55D', '65Q7D',
          '75Q7D', '86Q7D', '100Q7D', '65Q8E', '75Q8E', '86Q8E', 'L100D']
    df = pd.read_excel(r"E:\计划物料\MaterialJob\pythonProject\src\source\测试数据.xlsx", sheet_name="表2")
    df.columns = ["product_model"]
    # df = df[["product_model"]]
    # df['product_model'] = st
    df['product_level'] = 1
    df.drop_duplicates(["product_model"], keep='first', inplace=True)
    print(df)
    from src.db.dbhelper import pandas_to_sql
    pandas_to_sql(df, "product_model_td", if_exists='append')
    print(df.shape[0])


def insert_material(pa, sheet_name):
    df = pd.read_excel(pa, sheet_name=sheet_name)
    df.columns = ["m_type", "name"]
    df.drop_duplicates(["m_type", "name"], keep='first', inplace=True)
    print(df)
    from src.db.dbhelper import pandas_to_sql
    pandas_to_sql(df, "material_type", if_exists='replace')


def insert_important_material(df):
    df = pd.read_excel(pa, sheet_name=sheet_name)
    df = df[["物料"]]
    df.drop_duplicates(["物料"], keep='first', inplace=True)
    df.columns = ["name"]
    df["m_type"] = ""
    # print(df)
    material_sql = """select m_type , name  from material_type"""
    source_df = query_sql_by_pandas(material_sql)
    for idx, row in df.iterrows():
        material_code = row["name"]
        filter_df = source_df[source_df["name"] == material_code]
        if not filter_df.empty:
            df.loc[idx, "m_type"] = filter_df.iloc[0]["m_type"]
            upd_sql = f"update material_type set is_important = 1 where name = '{material_code}' "
            result = execute_sql(upd_sql)
            print(result)
    print(df)
    from src.db.dbhelper import pandas_to_sql


def init_data(pa, sheet_name):
    df = pd.read_excel(pa, sheet_name=sheet_name, usecols=["型号", "物料编码"])
    xh_df = df[["型号"]].copy()
    xh_df.drop_duplicates(["型号"], keep="first", inplace=True)
    all_product_models = xh_df["型号"].tolist()
    material_sql = """select id, m_type , name, is_important  from material_type """
    product_sql = "select id, product_model  from product_model_td"
    material_df = query_sql_by_pandas(material_sql)
    product_df = query_sql_by_pandas(product_sql)
    print(material_df)
    pro_ids = []
    product_models = []
    relations = []
    scales = []

    for product in all_product_models:
        print(product)
        filter_df = df[df["型号"] == product].copy()
        if not filter_df.empty:
            pro_filter_df = product_df[product_df["product_model"] == product]
            if pro_filter_df.empty:
                print("型号不存在")
                continue
            product_id = pro_filter_df.iloc[0]["id"]
            # 型号组成
            # 标记
            filter_df.drop_duplicates(["型号", "物料编码"], keep="first", inplace=True)
            for idx1, row1 in filter_df.iterrows():
                xh = row1["型号"]
                bm = row1["物料编码"]
                # 寻找物料编码 看是否是重点材料
                f = material_df[(material_df["name"] == bm) & (material_df["is_important"] == 1)]
                if not f.empty:
                    material_id = f.iloc[0]["id"]
                    pro_ids.append(product_id)
                    product_models.append(product)
                    relations.append(material_id)
                    scales.append(1)
    new_df = pd.DataFrame(columns=["pro_id", "product_model", "relation", "scale"])
    new_df["pro_id"] = pro_ids
    new_df["product_model"] = product_models
    new_df["relation"] = relations
    new_df["scale"] = scales
    from src.db.dbhelper import pandas_to_sql
    pandas_to_sql(new_df, "product_model_connect", if_exists="append")


if __name__ == '__main__':
    # insert_material(r"E:\计划物料\MaterialJob\pythonProject\src\source\测试数据(1).xlsx", sheet_name="表2")
    import pandas as pd

    pa = r"E:\计划物料\MaterialJob\pythonProject\src\source\测试数据(1).xlsx"
    sheet_name = "紧缺物料库存"
    df = pd.read_excel(pa, sheet_name=sheet_name, usecols=["物料", "非限制使用的库存"])
    print(df)
    for idx, row in df.iterrows():
        material_name = row["物料"]
        quantity = row["非限制使用的库存"]
        sql = f"""update material_type_quantity set material_quantity = {quantity} where material_name  = '{material_name}' """
        res = execute_sql(sql)
        print(res)
    # pandas_to_sql(df, "material_type", if_exists='append')
    # new_product_list = df["型号清单"].tolist()
    # se = "select product_model from product_model_td"
    # source_df = query_sql_by_pandas(se)
    # source_df_list = source_df["product_model"].tolist()
    # for product_model in new_product_list:
    #     if product_model not in source_df_list:
    #         print(product_model)

    # insert_material()
